<?php
/**
 * Excel数据处理脚本 - 将sbb.xlsx转换为多语言数据库SQL
 * 处理顺序：D列(分类) -> B列+C列(症状) -> A列+B列+C列(汤药)
 */

// 翻译映射表
class MultiLanguageTranslator {
    private $translations = [
        'en' => [
            '头颈部症状' => 'Head & Neck Symptoms',
            '呼吸系统症状' => 'Respiratory Symptoms',
            '消化系统症状' => 'Digestive Symptoms',
            '循环系统症状' => 'Circulatory Symptoms',
            '其他症状' => 'Other Symptoms',
            '头痛' => 'Headache',
            '头晕' => 'Dizziness',
            '咳嗽' => 'Cough',
            '恶心' => 'Nausea',
            '发热' => 'Fever',
            '失眠' => 'Insomnia',
            '腹痛' => 'Abdominal Pain',
            '便秘' => 'Constipation',
            '腹泻' => 'Diarrhea',
            '心悸' => 'Palpitation',
            '胸闷' => 'Chest Tightness',
            '气短' => 'Shortness of Breath',
            '疲劳' => 'Fatigue',
            '食欲不振' => 'Loss of Appetite',
            '桂枝汤' => 'Cinnamon Twig Decoction',
            '小青龙汤' => 'Minor Bluegreen Dragon Decoction',
            '银翘散' => 'Honeysuckle and Forsythia Powder',
            '麻黄汤' => 'Ephedra Decoction',
            '大青龙汤' => 'Major Bluegreen Dragon Decoction'
        ],
        'ja' => [
            '头颈部症状' => '頭頸部症状',
            '呼吸系统症状' => '呼吸器症状',
            '消化系统症状' => '消化器症状',
            '循环系统症状' => '循環器症状',
            '其他症状' => 'その他の症状',
            '头痛' => '頭痛',
            '头晕' => 'めまい',
            '咳嗽' => '咳',
            '恶心' => '吐き気',
            '发热' => '発熱',
            '失眠' => '不眠症',
            '腹痛' => '腹痛',
            '便秘' => '便秘',
            '腹泻' => '下痢',
            '心悸' => '動悸',
            '胸闷' => '胸苦しさ',
            '气短' => '息切れ',
            '疲劳' => '疲労',
            '食欲不振' => '食欲不振',
            '桂枝汤' => '桂枝湯',
            '小青龙汤' => '小青竜湯',
            '银翘散' => '銀翹散',
            '麻黄汤' => '麻黄湯',
            '大青龙汤' => '大青竜湯'
        ],
        'be' => [
            '头颈部症状' => 'Сімптомы галавы і шыі',
            '呼吸系统症状' => 'Сімптомы дыхальнай сістэмы',
            '消化系统症状' => 'Сімптомы стрававальнай сістэмы',
            '循环系统症状' => 'Сімптомы кровазвярнення',
            '其他症状' => 'Іншыя сімптомы',
            '头痛' => 'Галаўны боль',
            '头晕' => 'Запаморкі',
            '咳嗽' => 'Кашаль',
            '恶心' => 'Млосць',
            '发热' => 'Ліхаманка',
            '失眠' => 'Бяссонніца',
            '腹痛' => 'Боль у жываце',
            '便秘' => 'Закрэп',
            '腹泻' => 'Панос',
            '心悸' => 'Сэрцабіенне',
            '胸闷' => 'Цяжкасць у грудзях',
            '气短' => 'Задышка',
            '疲劳' => 'Стомленасць',
            '食欲不振' => 'Страта апетыту',
            '桂枝汤' => 'Адвар корніца карыцы',
            '小青龙汤' => 'Малы сіні дракон адвар',
            '银翘散' => 'Парашок жымолость і форзіцыя',
            '麻黄汤' => 'Адвар эфедры',
            '大青龙汤' => 'Вялікі сіні дракон адвар'
        ]
    ];

    public function translate($text, $lang) {
        $text = trim($text);
        if (isset($this->translations[$lang][$text])) {
            return $this->translations[$lang][$text];
        }
        // 如果没有预设翻译，返回带语言标识的原文
        $prefix = ['en' => 'EN:', 'ja' => 'JA:', 'be' => 'BE:'];
        return ($prefix[$lang] ?? '') . $text;
    }
}

class ExcelToSqlProcessor {
    private $translator;
    private $categories = [];
    private $symptoms = [];
    private $sqlStatements = [];

    public function __construct() {
        $this->translator = new MultiLanguageTranslator();
    }

    private function cleanText($text) {
        if (empty($text)) return '';

        $text = trim($text);
        // 替换中文逗号为英文逗号，去除所有空格
        $text = preg_replace('/[，,]\s*/', ',', $text);
        $text = preg_replace('/\s+/', '', $text);
        return $text;
    }

    private function splitByComma($text) {
        if (empty($text)) return [];

        $items = explode(',', $text);
        $items = array_map('trim', $items);
        $items = array_filter($items, function($item) {
            return !empty($item);
        });
        return array_unique($items);
    }

    private function escapeString($str) {
        return mysqli_real_escape_string(null, $str);
    }

    public function processFromArray($data) {
        echo "开始处理数据，共 " . count($data) . " 行\n";

        // 1. 处理分类（D列）
        $this->processCategories($data);

        // 2. 处理症状（B列和C列）
        $this->processSymptoms($data);

        // 3. 处理汤药（A列+B列+C列）
        $this->processFormulas($data);

        // 4. 生成SQL文件
        $outputPath = __DIR__ . '/sbb_processed.sql';
        $this->generateSqlFile($outputPath);

        return $outputPath;
    }

    private function processCategories($data) {
        echo "开始处理分类数据...\n";

        $allCategories = [];
        foreach ($data as $row) {
            $categories = $this->splitByComma($row[3] ?? ''); // D列
            $allCategories = array_merge($allCategories, $categories);
        }

        $allCategories = array_unique($allCategories);
        $allCategories = array_filter($allCategories);
        // 不添加"其他症状"，因为数据库中已经存在

        echo "发现 " . count($allCategories) . " 个分类\n";

        $this->sqlStatements[] = "-- ==========================================";
        $this->sqlStatements[] = "-- 症状分类表数据";
        $this->sqlStatements[] = "-- ==========================================";

        $categoryId = 1;
        foreach ($allCategories as $category) {
            if (empty($category)) continue;

            $nameEn = $this->translator->translate($category, 'en');
            $nameJa = $this->translator->translate($category, 'ja');
            $nameBe = $this->translator->translate($category, 'be');

            $sql = "INSERT INTO `ai_symptom_categories` (
    `name_zh_cn`, `name_en_us`, `name_ja_jp`, `name_be_by`,
    `description_zh_cn`, `description_en_us`, `description_ja_jp`, `description_be_by`,
    `sort_order`, `is_active`
) VALUES (
    '{$category}', '{$nameEn}', '{$nameJa}', '{$nameBe}',
    '{$category}相关症状', '{$nameEn} related symptoms', '{$nameJa}関連症状', '{$nameBe} звязаныя сімптомы',
    {$categoryId}, TRUE
);";

            $this->sqlStatements[] = $sql;
            $this->categories[$category] = $categoryId;
            $categoryId++;
        }

        echo "生成了 " . count($this->categories) . " 个分类的SQL语句\n";
    }

    private function findCategoryForSymptom($symptom, $rowCategories) {
        if (empty($rowCategories)) {
            return 999; // 假设"其他症状"的ID为999，实际需要查询数据库确认
        }

        foreach ($rowCategories as $category) {
            if (isset($this->categories[$category])) {
                return $this->categories[$category];
            }
        }

        return 999; // 假设"其他症状"的ID为999，实际需要查询数据库确认
    }

    private function processSymptoms($data) {
        echo "开始处理症状数据...\n";

        $symptomCategoryMap = [];

        foreach ($data as $row) {
            $rowCategories = $this->splitByComma($row[3] ?? ''); // D列

            // 处理主证状（B列）
            $mainSymptoms = $this->splitByComma($row[1] ?? '');
            foreach ($mainSymptoms as $symptom) {
                if (!empty($symptom) && !isset($symptomCategoryMap[$symptom])) {
                    $symptomCategoryMap[$symptom] = $rowCategories;
                }
            }

            // 处理兼症状（C列）
            $sideSymptoms = $this->splitByComma($row[2] ?? '');
            foreach ($sideSymptoms as $symptom) {
                if (!empty($symptom) && !isset($symptomCategoryMap[$symptom])) {
                    $symptomCategoryMap[$symptom] = $rowCategories;
                }
            }
        }

        echo "发现 " . count($symptomCategoryMap) . " 个症状\n";

        $this->sqlStatements[] = "";
        $this->sqlStatements[] = "-- ==========================================";
        $this->sqlStatements[] = "-- 症状表数据";
        $this->sqlStatements[] = "-- ==========================================";

        $symptomId = 1;
        foreach ($symptomCategoryMap as $symptom => $categories) {
            if (empty($symptom)) continue;

            $categoryId = $this->findCategoryForSymptom($symptom, $categories);

            $nameEn = $this->translator->translate($symptom, 'en');
            $nameJa = $this->translator->translate($symptom, 'ja');
            $nameBe = $this->translator->translate($symptom, 'be');

            $sql = "INSERT INTO `ai_symptoms` (
    `category_id`, `name_zh_cn`, `name_en_us`, `name_ja_jp`, `name_be_by`,
    `description_zh_cn`, `description_en_us`, `description_ja_jp`, `description_be_by`,
    `keywords_zh_cn`, `keywords_en_us`, `keywords_ja_jp`, `keywords_be_by`,
    `sort_order`, `is_active`
) VALUES (
    {$categoryId}, '{$symptom}', '{$nameEn}', '{$nameJa}', '{$nameBe}',
    '{$symptom}的症状描述', '{$nameEn} symptom description', '{$nameJa}の症状説明', '{$nameBe} апісанне сімптому',
    '{$symptom}', '{$nameEn}', '{$nameJa}', '{$nameBe}',
    {$symptomId}, TRUE
);";

            $this->sqlStatements[] = $sql;
            $this->symptoms[$symptom] = $symptomId;
            $symptomId++;
        }

        echo "生成了 " . count($this->symptoms) . " 个症状的SQL语句\n";
    }

    private function processFormulas($data) {
        echo "开始处理汤药数据...\n";

        $this->sqlStatements[] = "";
        $this->sqlStatements[] = "-- ==========================================";
        $this->sqlStatements[] = "-- 汤药表数据";
        $this->sqlStatements[] = "-- ==========================================";

        $formulaId = 1;
        foreach ($data as $row) {
            $formulaName = trim($row[0] ?? ''); // A列
            if (empty($formulaName)) continue;

            // 获取主证状和兼症状ID
            $mainSymptoms = $this->splitByComma($row[1] ?? ''); // B列
            $sideSymptoms = $this->splitByComma($row[2] ?? ''); // C列

            $mainSymptomIds = [];
            foreach ($mainSymptoms as $symptom) {
                if (isset($this->symptoms[$symptom])) {
                    $mainSymptomIds[] = $this->symptoms[$symptom];
                }
            }

            $sideSymptomIds = [];
            foreach ($sideSymptoms as $symptom) {
                if (isset($this->symptoms[$symptom])) {
                    $sideSymptomIds[] = $this->symptoms[$symptom];
                }
            }

            $nameEn = $this->translator->translate($formulaName, 'en');
            $nameJa = $this->translator->translate($formulaName, 'ja');
            $nameBe = $this->translator->translate($formulaName, 'be');

            $sql = "INSERT INTO `ai_formulas` (
    `name_zh_cn`, `name_en_us`, `name_ja_jp`, `name_be_by`,
    `description_zh_cn`, `description_en_us`, `description_ja_jp`, `description_be_by`,
    `ingredients_zh_cn`, `ingredients_en_us`, `ingredients_ja_jp`, `ingredients_be_by`,
    `usage_method_zh_cn`, `usage_method_en_us`, `usage_method_ja_jp`, `usage_method_be_by`,
    `effects_zh_cn`, `effects_en_us`, `effects_ja_jp`, `effects_be_by`,
    `main_symptom_ids`, `side_symptom_ids`,
    `sort_order`, `is_active`
) VALUES (
    '{$formulaName}', '{$nameEn}', '{$nameJa}', '{$nameBe}',
    '{$formulaName}的中医汤药', '{$nameEn} Traditional Chinese Medicine', '{$nameJa}の中医学薬', '{$nameBe} традыцыйная кітайская медыцына',
    '具体配方待补充', 'Specific formula to be added', '具体的な処方は後で追加', 'Канкрэтная формула будзе дададзена',
    '煎服方法待补充', 'Decoction method to be added', '煎じ方は後で追加', 'Метад прыгатавання будзе дададзены',
    '功效作用待补充', 'Effects to be added', '効果は後で追加', 'Эфект будзе дададзены',
    '" . implode(',', $mainSymptomIds) . "', '" . implode(',', $sideSymptomIds) . "',
    {$formulaId}, TRUE
);";

            $this->sqlStatements[] = $sql;
            $formulaId++;
        }

        echo "生成了 " . ($formulaId - 1) . " 个汤药的SQL语句\n";
    }

    private function generateSqlFile($outputPath) {
        $content = "-- ==========================================\n";
        $content .= "-- Excel数据导入SQL文件\n";
        $content .= "-- 生成时间: " . date('Y-m-d H:i:s') . "\n";
        $content .= "-- 数据来源: sbb.xlsx\n";
        $content .= "-- ==========================================\n\n";

        $content .= "SET NAMES utf8mb4;\n";
        $content .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";

        foreach ($this->sqlStatements as $sql) {
            $content .= $sql . "\n\n";
        }

        $content .= "SET FOREIGN_KEY_CHECKS = 1;\n";
        $content .= "-- SQL文件生成完成\n";

        if (file_put_contents($outputPath, $content)) {
            echo "SQL文件已生成: {$outputPath}\n";
        } else {
            throw new Exception("生成SQL文件失败: {$outputPath}");
        }
    }
}

// 示例数据（由于无法直接读取Excel，提供示例数据格式）
$sampleData = [
    // A列：汤药名称, B列：主证状, C列：兼症状, D列：所属分类
    ['桂枝汤', '头痛,恶寒', '发热,汗出', '呼吸系统症状,其他症状'],
    ['小青龙汤', '咳嗽,气喘', '恶寒,无汗', '呼吸系统症状'],
    ['银翘散', '发热,头痛', '咽痛,口渴', '头颈部症状,呼吸系统症状'],
    ['麻黄汤', '恶寒,无汗', '头痛,身痛', '呼吸系统症状,头颈部症状'],
    ['大青龙汤', '恶寒,发热', '烦躁,口渴', '呼吸系统症状,其他症状'],
    ['五苓散', '小便不利,水肿', '头晕,恶心', '循环系统症状,消化系统症状'],
    ['平胃散', '腹胀,食欲不振', '恶心,口淡', '消化系统症状'],
    ['六君子汤', '食欲不振,腹胀', '疲劳,便溏', '消化系统症状'],
    ['逍遥散', '胸胁胀痛,抑郁', '月经不调,头晕', '其他症状,头颈部症状'],
    ['补中益气汤', '疲劳,气短', '食欲不振,腹泻', '消化系统症状,其他症状']
];

try {
    echo "=== Excel数据处理开始 ===\n";

    $processor = new ExcelToSqlProcessor();
    $outputFile = $processor->processFromArray($sampleData);

    echo "\n=== 处理完成 ===\n";
    echo "输出文件: {$outputFile}\n";
    echo "分类数量: " . count($processor->categories ?? []) . "\n";
    echo "症状数量: " . count($processor->symptoms ?? []) . "\n";

} catch (Exception $e) {
    echo "处理失败: " . $e->getMessage() . "\n";
}

echo "\n注意事项：\n";
echo "1. 由于无法直接读取Excel文件，以上使用的是示例数据。\n";
echo "2. 请手动替换\$sampleData数组中的数据为实际Excel内容，或添加Excel读取功能。\n";
echo "3. 脚本假设'其他症状'分类的ID为999，请先执行以下SQL查询确认实际ID：\n";
echo "   SELECT id FROM ai_symptom_categories WHERE name_zh_cn = '其他症状';\n";
echo "4. 然后将脚本中的999替换为实际的ID值。\n";
?>